Banco de Dados Sistema Escolar
Aqui temos um banco de dados para armazenar informações de um sistema escolar. Neste modelo é possível gerenciar alunos, professores, cursos, disciplinas, turmas e matrículas.



Visão Geral do Modelo

O sistema é composto basicamente por seis grandes áreas:

  1. Alunos

  2. Professores

  3. Cursos

  4. Turmas

  5. Disciplinas

  6. Matrícula


Entidades (tabelas) e Atributos (colunas)

PESSOA

  • id_pessoa (PK)

  • nome

  • data_nascimento

  • sexo

Obs: Entidade genérica que representa indivíduos do sistema.


ALUNO

  • id_aluno (PK)

  • id_pessoa (FK, UNIQUE)

Obs: Especialização de Pessoa.


PROFESSOR

  • id_professor (PK)

  • formacao

  • id_pessoa (FK, UNIQUE)

Obs: Especialização de Pessoa.


EMAIL

  • id_email (PK)

  • email

  • id_pessoa (FK)

Obs: Uma pessoa pode possuir vários e-mails.


TELEFONE

  • id_telefone (PK)

  • telefone

  • id_pessoa (FK)

Obs: Uma pessoa pode possuir vários telefones.


CURSO

  • id_curso (PK)

  • nome

  • descricao

  • data_inicio

  • data_fim


DISCIPLINA

  • id_disciplina (PK)

  • nome

  • carga_horaria

  • data_inicio

  • data_fim

  • id_curso (FK)

  • id_professor (FK)


TURMA

  • id_turma (PK)

  • nome

  • ano

  • semestre

  • id_curso (FK)


MATRÍCULA

  • id_matricula (PK)

  • data_matricula

  • status

  • id_aluno (FK)

  • id_turma (FK)


Relacionamentos e Cardinalidades

  • PESSOA (1,N) — EMAIL

    Uma pessoa pode ter vários e-mails.

  • PESSOA (1,N) — TELEFONE

    Uma pessoa pode ter vários telefones.

  • PESSOA (1,1) — ALUNO (especialização)

    Um aluno é uma pessoa.

  • PESSOA (1,1) — PROFESSOR (especialização)

    Um professor é uma pessoa.

  • CURSO (1,N) — DISCIPLINA

    Um curso é composto por uma ou mais disciplinas.

  • PROFESSOR (1,N) — DISCIPLINA

    Um professor pode ministrar várias disciplinas.

  • CURSO (1,N) — TURMA

    Um curso pode ter várias turmas.

  • ALUNO (1,N) — MATRÍCULA — (1,N) TURMA

    Relacionamento muitos-para-muitos entre aluno e turma.




Diagrama Entidade Relacionamento



Script de Criação do Banco de Dados

DROP DATABASE IF EXISTS sistema_escolar;
CREATE DATABASE sistema_escolar;
USE sistema_escolar;

CREATE TABLE pessoa (
    id_pessoa INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(100) NOT NULL,
    data_nascimento DATE,
    sexo CHAR(1)
);

CREATE TABLE email (
    id_email INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(150) NOT NULL,
    id_pessoa INT NOT NULL,
    CONSTRAINT fk_email_pessoa
        FOREIGN KEY (id_pessoa) REFERENCES pessoa(id_pessoa)
);

CREATE TABLE telefone (
    id_telefone INT AUTO_INCREMENT PRIMARY KEY,
    telefone VARCHAR(20) NOT NULL,
    id_pessoa INT NOT NULL,
    CONSTRAINT fk_telefone_pessoa
        FOREIGN KEY (id_pessoa) REFERENCES pessoa(id_pessoa)
);

CREATE TABLE aluno (
    id_aluno INT AUTO_INCREMENT PRIMARY KEY,
    id_pessoa INT NOT NULL UNIQUE,
    CONSTRAINT fk_aluno_pessoa
        FOREIGN KEY (id_pessoa) REFERENCES pessoa(id_pessoa)
);

CREATE TABLE professor (
    id_professor INT AUTO_INCREMENT PRIMARY KEY,
    formacao VARCHAR(100),
    id_pessoa INT NOT NULL UNIQUE,
    CONSTRAINT fk_professor_pessoa
        FOREIGN KEY (id_pessoa) REFERENCES pessoa(id_pessoa)
);

CREATE TABLE curso (
    id_curso INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(100) NOT NULL,
    descricao VARCHAR(255),
    data_inicio DATE NOT NULL,
    data_fim DATE NOT NULL
);

CREATE TABLE disciplina (
    id_disciplina INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(100) NOT NULL,
    carga_horaria INT NOT NULL,
    data_inicio DATE NOT NULL,
    data_fim DATE NOT NULL,
    id_curso INT NOT NULL,
    id_professor INT NOT NULL,
    CONSTRAINT fk_disciplina_curso
        FOREIGN KEY (id_curso) REFERENCES curso(id_curso),
    CONSTRAINT fk_disciplina_professor
        FOREIGN KEY (id_professor) REFERENCES professor(id_professor)
);

CREATE TABLE turma (
    id_turma INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(50) NOT NULL,
    ano INT NOT NULL,
    semestre INT NOT NULL,
    id_curso INT NOT NULL,
    CONSTRAINT fk_turma_curso
        FOREIGN KEY (id_curso) REFERENCES curso(id_curso)
);


CREATE TABLE matricula (
    id_matricula INT AUTO_INCREMENT PRIMARY KEY,
    data_matricula DATE NOT NULL,
    status VARCHAR(20),
    id_aluno INT NOT NULL,
    id_turma INT NOT NULL,
    CONSTRAINT fk_matricula_aluno
        FOREIGN KEY (id_aluno) REFERENCES aluno(id_aluno),
    CONSTRAINT fk_matricula_turma
        FOREIGN KEY (id_turma) REFERENCES turma(id_turma)
);

INSERT INTO pessoa (nome, data_nascimento, sexo) VALUES
('João Pereira', '2002-05-10', 'M'),
('Maria Oliveira', '2001-08-22', 'F'),
('Pedro Santos', '2003-03-15', 'M'),
('Carlos Silva', '1980-04-12', 'M'),
('Ana Souza', '1985-09-30', 'F');

INSERT INTO email (email, id_pessoa) VALUES
('joao@email.com', 1),
('maria@email.com', 2),
('pedro@email.com', 3),
('carlos@escola.com', 4),
('ana@escola.com', 5);

INSERT INTO telefone (telefone, id_pessoa) VALUES
('11988880001', 1),
('11988880002', 2),
('11988880003', 3),
('11999990001', 4),
('11999990002', 5);

INSERT INTO aluno (id_pessoa) VALUES
(1),
(2),
(3);

INSERT INTO professor (formacao, id_pessoa) VALUES
('Matemática', 4),
('Computação', 5);

INSERT INTO curso (nome, descricao, data_inicio, data_fim) VALUES
('Sistemas de Informação', 'Curso de tecnologia', '2024-02-01', '2027-12-15');

INSERT INTO disciplina (nome, carga_horaria, data_inicio, data_fim, id_curso, id_professor) VALUES
('Algoritmos', 80, '2024-02-15', '2024-06-30', 1, 2),
('Banco de Dados', 80, '2024-08-01', '2024-12-15', 1, 2);

INSERT INTO turma (nome, ano, semestre, id_curso) VALUES
('SI-2024-1', 2024, 1, 1);

INSERT INTO matricula (data_matricula, status, id_aluno, id_turma) VALUES
('2024-02-10', 'ATIVA', 1, 1),
('2024-02-10', 'ATIVA', 2, 1);



Exercícios de SQL – Sistema Escolar

NÍVEL 1 – Consultas Básicas (SELECT)

  1. Liste todos os alunos, exibindo apenas o nome e o sexo.

  2. Liste todos os professores e suas formações.

  3. Exiba todos os cursos com suas datas de início e fim.

  4. Liste todas as disciplinas cadastradas.

  5. Liste todas as turmas do ano de 2024.


NÍVEL 2 – SELECT com WHERE e ORDER BY

  1. Liste os alunos do sexo feminino.

  2. Liste os professores com formação em Computação.

  3. Liste as disciplinas com carga horária maior ou igual a 80 horas.

  4. Exiba as turmas do 1º semestre, ordenadas pelo nome.

  5. Liste os cursos que terminam após o ano de 2026.


NÍVEL 3 – JOIN (Relacionamentos)

  1. Liste o nome do aluno e o nome da turma em que ele está matriculado.

  2. Liste o nome do aluno e o nome do curso que ele está cursando.

  3. Liste o nome do professor e as disciplinas que ele ministra.

  4. Liste as disciplinas junto com o nome do curso ao qual pertencem.

  5. Liste o nome da turma e o nome do curso correspondente.


NÍVEL 4 – JOIN com EMAIL e TELEFONE

  1. Liste o nome do aluno e seus e-mails.

  2. Liste o nome do professor e seus telefones.

  3. Liste todas as pessoas com seus respectivos e-mails.

  4. Liste os alunos que possuem mais de um telefone.

  5. Liste as pessoas que não possuem e-mail cadastrado.


NÍVEL 5 – Funções de Agregação e GROUP BY

  1. Quantos alunos estão matriculados no sistema?

  2. Quantas disciplinas existem por curso?

  3. Quantos alunos existem em cada turma?

  4. Exiba o total de disciplinas ministradas por cada professor.

  5. Exiba o ano mais recente de início dos cursos.


NÍVEL 6 – Subconsultas

  1. Liste os alunos matriculados na turma "SI-2024-1".

  2. Liste os professores que não ministram nenhuma disciplina.

  3. Liste os cursos que possuem mais de uma disciplina.

  4. Liste os alunos que não estão matriculados em nenhuma turma.

  5. Liste as disciplinas cujo período está dentro do intervalo do curso.


NÍVEL 7 – Desafios (Integração Completa)

  1. Liste o nome do aluno, e-mail e telefone.

  2. Liste o nome do professor, sua formação e as disciplinas que ministra.

  3. Liste o nome do curso, suas disciplinas e o professor responsável.

  4. Liste os alunos, as turmas e os cursos, ordenados pelo nome do aluno.

  5. Gere um relatório com:

  • Nome do curso

  • Quantidade de turmas

  • Quantidade de disciplinas